package cn.ac.iscas.dao.provider.dameng;

import cn.ac.iscas.domain.job.Job;
import cn.ac.iscas.domain.job.JobSearch;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.jdbc.SQL;

import java.text.MessageFormat;
import java.util.List;

public class DamengJobMetadataProvider {


    public String selectByGroup(final JobSearch jobSearch) {
        String name = jobSearch.getName();
        String status = jobSearch.getStatus();
        String description = jobSearch.getDescription();
        String executeStartTime = jobSearch.getExecuteStartTime();
        String executeEndTime = jobSearch.getExecuteEndTime();
        String jobType = jobSearch.getJobType();
        String orderBy = jobSearch.getOrderBy();
        String isAsc = jobSearch.isAsc() ? " ASC " : " DESC ";
        int limit = jobSearch.getLimit();
        if (jobSearch == null) {
            return "";
        }

//        return new SQL(){{
//            SELECT( "*");
//            FROM( " ( SELECT _id, name, executeTime, status, duration, creator, " +
//                    " createTime, content, executeCount, cron, description, runInfo, preJobId, jobType, "  +
//                    " ROW_NUMBER() OVER ( PARTITION BY name ORDER BY executetime DESC NULLS LAST ) AS rn " +
//                    " FROM jobmetadata ) t ");
//            WHERE("rn = 1");
//            if(name != null && !name.trim().isEmpty()) {
//                WHERE("name LIKE CONCAT('%',#{name},'%')");
//            }
//            if(status != null && !status.trim().isEmpty()){
//                WHERE("status = #{status}");
//            }
//            if(description != null && !description.trim().isEmpty()) {
//                WHERE("description LIKE CONCAT('%',#{description},'%')");
//            }
//            if(executeStartTime != null){
//                WHERE(" #{executeStartTime} <= executeTime");
//            }
//            if(executeEndTime != null){
//                WHERE(" #{executeEndTime} >= executeTime");
//            }
//            if(jobType != null && !jobType.trim().isEmpty()){
//                WHERE("jobType = #{jobType}");
//            }
//            ORDER_BY("executetime DESC NULLS LAST");
//
//        }}.toString();

        return new SQL(){{
            SELECT( "t.*");
            FROM("MIDDLE_PLATFORM.\"JOBMETADATA\" t");
            INNER_JOIN("(SELECT name, MAX(executeTime) AS executeTime FROM MIDDLE_PLATFORM.\"JOBMETADATA\" GROUP BY name) s ON s.name = t.name AND s.executeTime = t.executeTime");
            if(name != null && !name.trim().isEmpty()) {
                WHERE("t.name LIKE CONCAT('%',#{name},'%')");
            }
            if(status != null && !status.trim().isEmpty()){
                WHERE("t.status = #{status}");
            }
            if(description != null && !description.trim().isEmpty()) {
                WHERE("t.description LIKE CONCAT('%',#{description},'%')");
            }
            if(executeStartTime != null){
                WHERE(" #{executeStartTime} <= t.executeTime");
            }
            if(executeEndTime != null){
                WHERE(" #{executeEndTime} >= t.executeTime");
            }
            if(jobType != null && !jobType.trim().isEmpty()){
                WHERE("t.jobType = #{jobType}");
            }
            ORDER_BY("t.executeTime DESC NULLS LAST");

        }}.toString();


    }

    //根据查询条件查询Job
    public String select(final JobSearch jobSearch){
        String name = jobSearch.getName();
        String status = jobSearch.getStatus();
        String description = jobSearch.getDescription();
        String executeStartTime = jobSearch.getExecuteStartTime();
        String executeEndTime = jobSearch.getExecuteEndTime();
        String jobType = jobSearch.getJobType();
        String orderBy = jobSearch.getOrderBy();
        String isAsc = jobSearch.isAsc() ? " ASC " : " DESC ";
        int limit = jobSearch.getLimit();
        if (jobSearch == null) {
            return "";
        }

        //todo:dameng, convert(varchar, content) AS content
        return new SQL(){{
            SELECT("_id, name, executeTime, status, duration, creator, " +
                    "createTime, content, executeCount, cron, description, runInfo, preJobId, jobType " );
            FROM("jobmetadata");
            if(name != null && !name.trim().isEmpty()) {
                WHERE("name LIKE CONCAT('%',#{name},'%')");
            }
            if(status != null && !status.trim().isEmpty()){
                WHERE("status = #{status}");
            }
            if(description != null && !description.trim().isEmpty()) {
                WHERE("description LIKE CONCAT('%',#{description},'%')");
            }
            if(executeStartTime != null){
                WHERE(" #{executeStartTime} <= executeTime");
            }
            if(executeEndTime != null){
                WHERE(" #{executeEndTime} >= executeTime");
            }
            if(jobType != null && !jobType.trim().isEmpty()){
                WHERE("jobType = #{jobType}");
            }
            if (orderBy != null && !orderBy.trim().isEmpty()) {
                ORDER_BY( orderBy + isAsc);
            } else {
                ORDER_BY("createTime DESC");
            }
            if (limit > 0) {
                LIMIT(limit);
            }

        }}.toString();
    }

    public String selectById(final String id){
        return new SQL(){{
            SELECT("_id, name, executeTime ,status ,duration, creator , " +
                    "createTime, content, executeCount, cron, description, runInfo, preJobId, jobType " );
            FROM("jobmetadata");
            if(id != null && !id.trim().isEmpty()) {
                WHERE("_id = #{id}");
            }
        }}.toString();
    }

    public String selectCount(final String jobName){
        return new SQL(){{
            SELECT("count(name) " );
            FROM("jobmetadata");
            if(jobName != null && !jobName.trim().isEmpty()) {
                WHERE("name = #{jobName}");
            }
        }}.toString();
    }

    public String selectPreJob(final String id){
        return new SQL(){{
            SELECT("_id, name, executeTime ,status ,duration, creator , " +
                    "createTime, content, executeCount, cron, description, runInfo, preJobId, jobType " );
            FROM("jobmetadata");
            if(id != null && !id.trim().isEmpty()) {
                WHERE("_id = #{id}");
            }
            WHERE("status IN ( 'WAITING', 'RUNNING' )");
        }}.toString();
    }

    public String insertBatch(List<Job> jobs){
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO jobmetadata ");
        sb.append("_id, name, executeTime ,status ,duration, creator , " +
                "createTime, content, executeCount, cron, description, runInfo, preJobId, jobType " );
        sb.append(" VALUES ");
        MessageFormat messageFormat = new MessageFormat("(" +
                "null," +
                "#'{'list[{0}].name}," +
                "#'{'list[{0}].executeTime}," +
                "#'{'list[{0}].status}," +
                "#'{'list[{0}].duration}," +
                "#'{'list[{0}].creator}," +
                "#'{'list[{0}].createTime}," +
                "#'{'list[{0}].content}," +
                "#'{'list[{0}].executeCount}," +
                "#'{'list[{0}].cron}," +
                "#'{'list[{0}].description}," +
                "#'{'list[{0}].runInfo}," +
                "#'{'list[{0}].preJobId}" +
                "#'{'list[{0}].jobType}" +
                ")");
        for (int i = 0; i < jobs.size(); i++) {
            sb.append(messageFormat.format(new Object[]{i}));
            if (i < jobs.size() - 1) {
                sb.append(",");
            }
        }
        return sb.toString();
    }

}
